--    Program: 		CAPPOIMP_ATTACHMENT_ATTACHED_DOCUMENT.sql
--    Description:  Import PO Attached Document information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Francis Lee
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_attached_document_id	number;
   l_document_id  			number;
   l_pk1_value				varchar2(100);
                    		                 		
   cursor cur_attachment is
      select * from CRST_FND_ATTACHED_DOCUMENTS;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_attachment loop  
   	 l_err_msg_tmp := '';
   	 
   	 begin
   	   select document_id_n 
   	   into   l_document_id
       from   crst_fnd_documents
       where  document_id = cur.document_ID;
	 exception
	 	 when others then
	 	   l_err_msg_tmp := 'New DOCUMENT_ID not found';		
	 end;	
	 
	 if cur.ENTITY_NAME = 'PO_HEADERS' then
	   select CAPPOIMP_MAPPING.GetPOHeaderIDMapping(cur.PK1_VALUE)
	   into   l_pk1_value
	   from   dual;
	 elsif cur.ENTITY_NAME = 'PO_LINES' or cur.ENTITY_NAME = 'PO_IN_GA_LINES' then
	   select CAPPOIMP_MAPPING.GetPOLineIDMapping(cur.PK1_VALUE)
	   into   l_pk1_value
	   from   dual;
	 elsif cur.ENTITY_NAME = 'REQ_HEADERS' then
	   select CAPPOIMP_MAPPING.GetPRHeaderIDMapping(cur.PK1_VALUE)
	   into   l_pk1_value
	   from   dual;
	 elsif cur.ENTITY_NAME = 'REQ_LINES' then
	   select CAPPOIMP_MAPPING.GetPRLineIDMapping(cur.PK1_VALUE)
	   into   l_pk1_value
	   from   dual;  
	 end if;	   	
     
     SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL 
     INTO l_attached_document_id
     FROM DUAL;
     
     begin
       -- import data
       insert into FND_ATTACHED_DOCUMENTS
       (
         ATTACHED_DOCUMENT_ID      ,
         DOCUMENT_ID               ,
         CREATION_DATE             ,
         CREATED_BY                ,
         LAST_UPDATE_DATE          ,
         LAST_UPDATED_BY           ,
         LAST_UPDATE_LOGIN         ,
         SEQ_NUM                   ,
         ENTITY_NAME               ,
         PK1_VALUE                 ,
         PK2_VALUE                 ,
         PK3_VALUE                 ,
         PK4_VALUE                 ,
         PK5_VALUE                 ,
         AUTOMATICALLY_ADDED_FLAG  ,
         PROGRAM_APPLICATION_ID    ,
         PROGRAM_ID                ,
         PROGRAM_UPDATE_DATE       ,
         REQUEST_ID                ,
         ATTRIBUTE_CATEGORY        ,
         ATTRIBUTE1                ,
         ATTRIBUTE2                ,
         ATTRIBUTE3                ,
         ATTRIBUTE4                ,
         ATTRIBUTE5                ,
         ATTRIBUTE6                ,
         ATTRIBUTE7                ,
         ATTRIBUTE8                ,
         ATTRIBUTE9                ,
         ATTRIBUTE10               ,
         ATTRIBUTE11               ,
         ATTRIBUTE12               ,
         ATTRIBUTE13               ,
         ATTRIBUTE14               ,
         ATTRIBUTE15               ,
         COLUMN1                   ,
         APP_SOURCE_VERSION        ,
         CATEGORY_ID               ,
         STATUS     
       )
       values
       (
         l_attached_document_id        ,
         l_document_id                 ,
         cur.CREATION_DATE             ,
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
         cur.LAST_UPDATE_DATE          ,
         CUR.LAST_UPDATED_BY,
         cur.LAST_UPDATE_LOGIN         ,
         cur.SEQ_NUM                   ,
         cur.ENTITY_NAME               ,
         l_pk1_value                   ,
         cur.PK2_VALUE                 ,
         cur.PK3_VALUE                 ,
         cur.PK4_VALUE                 ,
         cur.PK5_VALUE                 ,
         cur.AUTOMATICALLY_ADDED_FLAG  ,
         cur.PROGRAM_APPLICATION_ID    ,
         cur.PROGRAM_ID                ,
         cur.PROGRAM_UPDATE_DATE       ,
         cur.REQUEST_ID                ,
         cur.ATTRIBUTE_CATEGORY        ,
         cur.ATTRIBUTE1                ,
         cur.ATTRIBUTE2                ,
         cur.ATTRIBUTE3                ,
         cur.ATTRIBUTE4                ,
         cur.ATTRIBUTE5                ,
         cur.ATTRIBUTE6                ,
         cur.ATTRIBUTE7                ,
         cur.ATTRIBUTE8                ,
         cur.ATTRIBUTE9                ,
         cur.ATTRIBUTE10               ,
         cur.ATTRIBUTE11               ,
         cur.ATTRIBUTE12               ,
         cur.ATTRIBUTE13               ,
         cur.ATTRIBUTE14               ,
         cur.ATTRIBUTE15               ,
         cur.COLUMN1                   ,
         cur.APP_SOURCE_VERSION        ,
         cur.CATEGORY_ID               ,
         cur.STATUS         
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;
     
	 if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_ATTACHMENT_ATTACHED_DOCUMENT', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
